package com.wobangkj.common.repository;

import com.wobangkj.common.model.crud.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Component;


import java.util.List;
import java.util.Map;

@Component
public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "select id from User where unionid=?1")
    List getIdByUnionid(String unionid);

    @Query(nativeQuery = true, value="select * from  user  where openid=:openid ")
    User getByOpenid(String openid);

    @Query(nativeQuery = true, value="select skey from  user  where openid=:openid ")
    List<User> getskeyByOpenid(String openid);


    @Query(nativeQuery = true, value="select * from  user  where name like CONCAT('%',:name,'%') ")
    List<User> findByNameLike(String name);




    /**
     * jpa 原生sql
     *
     * @param name
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true,
            value = "select * from user " +
                    "where name like CONCAT('%',:name,'%') ")
    Page<User> findByName(String name, Pageable pageable);

    @Query(nativeQuery = true,
            value = "select collect_text_id from collect where collector_id=:id and collect_class=0 ")
    Long[] findMyJob(String id);

    @Query(nativeQuery = true,
            value = "select collect_text_id from collect where collector_id=:id and collect_class=1 ")
    Long[] findMyShare(String id);

    @Query(nativeQuery = true, value = "select e.*,u.`name`,u.headimg,u.mst_job,u.school,u.grade,u.major,u.college from (select j.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from job j inner join company cp on j.job_company = cp.id group by j.id) e inner join user u on e.job_publisher_id = u.id where e.id= :id ;")
    List<Map> jobIdList(Long id);

    @Query(nativeQuery = true,value = "select e.*,cp.company_num,cp.business,cp.company_finance,cp.company_logo,cp.company_text,cp.company_name from (select s.*,u.major,u.name,u.mst_company_id,u.mst_job,u.grade,u.school,u.college,u.headimg from share s inner join user u on s.share_id = u.id ) e inner join company cp on e.mst_company_id = cp.id and e.id = :id group by e.id;") //原生SQL方法
    List<Map> shareIdList(Long id);


}
